﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace giaodien
{
    class ConnectData
    {
        static OleDbConnection cnt = new OleDbConnection();
        string cntStr = "provider=microsoft.ace.oledb.12.0; data source=QLCuaHangThoiTrang.accdb";
        
        public ConnectData()
        {
            Ketnoi();
        }
        void Ketnoi()
        {
            if (cnt.State == ConnectionState.Closed) 
            {
                cnt.ConnectionString = cntStr; cnt.Open(); 
            }
            cnt.Close();
        }
        public DataSet laydulieuDTS(string DataQ)
        {
            OleDbDataAdapter adt = new OleDbDataAdapter(DataQ, cnt);
            DataSet dts = new DataSet();
            adt.Fill(dts);
            cnt.Close();
            return dts;
        }
        public DataTable laydulieuBang(string DataQ)
        {
            OleDbDataAdapter adt = new OleDbDataAdapter(DataQ, cnt);
            DataTable dtt = new DataTable();
            adt.Fill(dtt);
            cnt.Close();
            return dtt;
        }
        public int tinhtong()
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cnt;
            if (cnt.State == ConnectionState.Closed)
                cnt.Open();
            cmd.CommandText = "select sum(TongTien) from hoadon";            
            return  Convert.ToInt32(cmd.ExecuteScalar());
        }
        public void LayBangTheoCauLenh(DataSet dt, string Query, string tenbang)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(Query, cnt);
            da.Fill(dt, tenbang);
        }

        #region Nhân Viên

        public DataTable laydulieutheocautruyvan(string sql)
        {
            DataTable dt = new DataTable();
            OleDbDataAdapter adt = new OleDbDataAdapter(sql, cnt);
            adt.Fill(dt);
            return dt;
        }
        public int themxoasuanv(string sql)
        {
            if (cnt.State == ConnectionState.Closed) cnt.Open();
            OleDbCommand lenh = new OleDbCommand(); 
            lenh.Connection = cnt;
            lenh.CommandText = sql;
            lenh.CommandType = CommandType.Text;
            return lenh.ExecuteNonQuery();
        }
        public int themnv(string manv,string tennv,string loai,string gioitinh, string dchinv,string sdtnv,string cmnd,string tendangnhap,string mk,string ngaysinh)
        {
            string sql="insert into nhanvien(manv,tennv,nsinhnv,sdtnv,dchinv,cmnd,tendangnhap,matkhau,loai,gioitinh) values('"+manv+"','"+tennv+"','"+ngaysinh+"','"+sdtnv+"','"+dchinv+"','"+cmnd+"','"+tendangnhap+"','"+mk+"','"+loai+"','"+gioitinh+"')";
            return themxoasuanv(sql);
        }
        public int xoanv(string manv)
        {
            string sql = "delete from nhanvien where manv='"+manv+"'";
            return themxoasuanv(sql);
        }
        public int suanv(string manv, string tennv, string loai, string gioitinh, string dchinv, string sdtnv, string cmnd, string tendangnhap, string mk, string ngaysinh)
        {
            string sql = "update nhanvien set tennv='"+tennv+"',nsinhnv='"+ngaysinh+"',sdtnv='"+sdtnv+"',dchinv='"+dchinv+"',cmnd='"+cmnd+"',tendangnhap='"+tendangnhap+"',matkhau='"+mk+"',loai='"+loai+"',gioitinh='"+gioitinh+"' where manv='"+manv+"'";
            if (cnt.State == ConnectionState.Open) cnt.Close();
            return themxoasuanv(sql);
        }

        #endregion
        public int ChayHam(string Query)
        {
            if (cnt.State == ConnectionState.Closed) cnt.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cnt;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = Query;
            return cmd.ExecuteNonQuery();
        }

        public DataTable ThemSanPham() {
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT ThemSanPham.LuotThemSP, NhanVien.TenNV, SanPham.TenSP, ThemSanPham.SoLuongThem, ThemSanPham.NgayThem FROM NhanVien INNER JOIN (SanPham INNER JOIN ThemSanPham ON SanPham.MaSP = ThemSanPham.SanPham) ON NhanVien.MaNV = ThemSanPham.NguoiThem",cnt);
            da.Fill(dt);
            return dt;
        }

        public string TaoMaHoaDon() {
            string MaHD="";
            string NgayHH = System.DateTime.Now.ToShortDateString();
            int SoNgay=DemSoHoaDonTheoNgay(NgayHH);
            MaHD = "HD" + NgayHH + SoNgay;
            return MaHD;
        }

        private int DemSoHoaDonTheoNgay(string Ngay) {
            int SoDong=0;
            string query = "select count(mahd) from HoaDon where mahd like '%"+Ngay+"%'";
            DataTable dtSoDong = new DataTable();
            dtSoDong = laydulieuBang(query);
            SoDong = int.Parse(dtSoDong.Rows[0][0].ToString());
            return SoDong+1;
        }

        public DataSet automa()
        {
            OleDbDataAdapter adt = new OleDbDataAdapter("select top 1 masp from sanpham order by masp desc", cnt);
            DataSet ds = new DataSet();
            adt.Fill(ds);
            cnt.Close();
            return ds;
        }

        #region loai
        public int themloai(string maloai, string tenloai, Boolean ttkichco)
        {
            string sql = "insert into loai(maloai,tenloai,ttkichco) values('" + maloai + "','" + tenloai + "'," + ttkichco + ")";
            return ChayHam(sql);
        }

        public int sualoai(string maloai, string tenloai, Boolean ttkichco)
        {
            string sql = "update loai set tenloai='" + tenloai + "',ttkichco=" + ttkichco + " where maloai='" + maloai + "'";
            return ChayHam(sql);
        }

        public int xoaloai(string maloai)
        {
            string sql = "delete from loai where maloai='" + maloai + "'";
            return ChayHam(sql);
        }
        #endregion

        #region sanpham
        public int themsp(string masp, string nhacc, string tensp, Boolean gioitinhsp, string kichco, string maloai, int soluongtk, decimal dongia)
        {
            string sql = "insert into sanpham(masp,nhacc,tensp,gioitinhsp,kichco,maloai,soluongtk,DonGia) values('" + masp + "','" + nhacc + "','" + tensp + "'," + gioitinhsp + ",'" + kichco + "','" + maloai + "'," + soluongtk + "," + dongia + ")";
            return ChayHam(sql);
        }

        public int suasp(string masp, string nhacc, string tensp, Boolean gioitinhsp, string kichco, string maloai, int soluongtk, decimal dongia)
        {
            string sql = "update sanpham set nhacc='" + nhacc + "',tensp='" + tensp + "',gioitinhsp=" + gioitinhsp + ",kichco='" + kichco + "',maloai='" + maloai + "',soluongtk=" + soluongtk + ",DonGia=" + dongia + " where masp='" + masp + "'";
            return ChayHam(sql);
        }

        public int xoasp(string masp)
        {
            string sql = "delete from SanPham where MaSP='" + masp + "'";
            return ChayHam(sql);
        }
        #endregion
    }
}